﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Edit_RobotQuotation : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        public string id;

        public frm_Edit_RobotQuotation()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.id = null;
            getQuotationList();
            this.quotationID.Properties.AutoComplete = true;
            getCompList();
            this.compID.Properties.AutoComplete = true;
            getSupplierList();
            this.supplierID.Properties.AutoComplete = true;
            getCurrencyList();
            this.currencySymbol.Properties.AutoComplete = true;
            getDeliveryList();
            this.priceType.Properties.AutoComplete = true;
            
        }

        //////////////////////////////////////////////
        /// Load Data
        ///////////////////////////////////////////////
        public bool LoadData()
        {
            bool ret = true;
            if (string.IsNullOrEmpty(id))
            {
                ret = false;
            }
            else
            {
                string comp = null;
                string supplier = null;
                string currency = null;
                string exchange = null;
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT * FROM [241_RobotPrice] WHERE ID=" + id;
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        comp = dataReader["CompID"].ToString().Trim();
                        supplier = dataReader["SupplierID"].ToString().Trim();
                        currency = dataReader["CurrencyID"].ToString().Trim();
                        for (int i = 0; i < this.quotationID.Properties.Items.Count; i++)
                        {
                            ComboboxItem item = (ComboboxItem)this.quotationID.Properties.Items[i];
                            if (item.Value.ToString().Trim() == dataReader["QuotationID"].ToString().Trim())
                            {
                                this.quotationID.SelectedIndex = i;
                            }
                        }                       

                        for (int i = 0; i < this.priceType.Properties.Items.Count; i++)
                        {
                            ComboboxItem item = (ComboboxItem)this.priceType.Properties.Items[i];
                            if (item.Value.ToString().Trim() == dataReader["PriceType"].ToString().Trim())
                            {
                                this.priceType.SelectedIndex = i;
                            }
                        }                        

                        exchange = dataReader["ExchangeRate"].ToString().Trim();
                        this.quantity1.Text = dataReader["Quantity1"].ToString().Trim();
                        this.quantity2.Text = dataReader["Quantity2"].ToString().Trim();
                        this.quantity3.Text = dataReader["Quantity3"].ToString().Trim();
                        this.quantity4.Text = dataReader["Quantity4"].ToString().Trim();
                        this.quantity5.Text = dataReader["Quantity5"].ToString().Trim();
                        this.price1.Text = dataReader["Price1"].ToString().Trim();
                        this.price2.Text = dataReader["Price2"].ToString().Trim();
                        this.price3.Text = dataReader["Price3"].ToString().Trim();
                        this.price4.Text = dataReader["Price4"].ToString().Trim();
                        this.price5.Text = dataReader["Price5"].ToString().Trim();

                        if ((this.quantity1.Text != this.quantity2.Text) || (this.quantity2.Text != this.quantity3.Text) ||
                            (this.quantity3.Text != this.quantity4.Text) || (this.quantity4.Text != this.quantity5.Text))
                        {
                            this.quantityBasePrice.Checked = true;
                        }
                        else
                        {
                            this.quantityBasePrice.Checked = false;
                        }

                        this.noteText.Text = dataReader["Note"].ToString().Trim();
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();

                    for (int i = 0; i < this.compID.Properties.Items.Count; i++)
                    {
                        ComboboxItem item = (ComboboxItem)this.compID.Properties.Items[i];
                        if (item.Value.ToString().Trim() == comp)
                        {
                            this.compID.SelectedIndex = i;
                        }
                    }

                    for (int i = 0; i < this.supplierID.Properties.Items.Count; i++)
                    {
                        ComboboxItem item = (ComboboxItem)this.supplierID.Properties.Items[i];
                        if (item.Value.ToString().Trim() == supplier)
                        {
                            this.supplierID.SelectedIndex = i;
                        }
                    }

                    for (int i = 0; i < this.currencySymbol.Properties.Items.Count; i++)
                    {
                        ComboboxItem item = (ComboboxItem)this.currencySymbol.Properties.Items[i];
                        if (item.Value.ToString().Trim() == currency)
                        {
                            this.currencySymbol.SelectedIndex = i;
                        }
                    }

                    this.exchangeRate.Text = exchange;
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    ret = false;
                    //MessageBox.Show(ex.Message);
                }
            }
            return ret;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        /////////////////////////////////////
        /// Load Export Quotation List
        /////////////////////////////////////
        private void getQuotationList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT QuotationID"
                    + " FROM [241_ExportQuotation]"
                    + " WHERE ComOrPart=1"
                    + " ORDER BY QuotationID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["QuotationID"].ToString();
                    item.Value = dataReader["QuotationID"].ToString();
                    this.quotationID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Quotation list");
            }
        }

        ///////////////////////////////////////
        /// Load Comp List
        ///////////////////////////////////////
        private void getCompList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT PartNo FROM [222_Part] ORDER BY PartNo";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["PartNo"].ToString();
                    item.Value = dataReader["PartNo"].ToString();
                    this.compID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Comp list");
            }
        }

        ///////////////////////////////////////
        /// Load Supplier List
        ///////////////////////////////////////
        private void getSupplierList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SupplierID, SupplierName FROM [222_Supplier] ORDER BY SupplierName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["SupplierName"].ToString();
                    item.Value = dataReader["SupplierID"].ToString();
                    this.supplierID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Supplier list");
            }
        }

        /////////////////////////////////////
        /// Load Delivery List
        /////////////////////////////////////
        private void getDeliveryList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT DeliveryTermID, DeliveryTermName FROM [222_DeliveryTerm] ORDER BY DeliveryTermName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["DeliveryTermName"].ToString();
                    item.Value = dataReader["DeliveryTermID"].ToString();
                    this.priceType.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {                
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Price Type list"); 
            }
        }

        /////////////////////////////////////
        /// Load Currency List
        /////////////////////////////////////
        private void getCurrencyList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT CurrencyID FROM [222_Currency] ORDER BY CurrencyID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["CurrencyID"].ToString();
                    item.Value = dataReader["CurrencyID"].ToString();
                    this.currencySymbol.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Currency list");
            }
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            string error = null;

            string supplier_id = null;
            string delivery_id = null;
            string currency = null;
            string note = null;

            int valueQuantity1 = 0, valueQuantity2 = 0, valueQuantity3 = 0, valueQuantity4 = 0, valueQuantity5 = 0;

            float valuePrice1 = 0, valuePrice2 = 0, valuePrice3 = 0, valuePrice4 = 0, valuePrice5 = 0, exchange=0;

            ComboboxItem item = new ComboboxItem();            

            /// Check Supplier.
            if (string.IsNullOrEmpty(error))
            {
                if (this.supplierID.SelectedIndex < 0)
                {
                    supplier_id = null;
                    //error = "Please select Supplier";
                }
                else
                {
                    item = (ComboboxItem)this.supplierID.SelectedItem;
                    supplier_id = item.Value.ToString().Trim();
                }
            }

            /// Check Price Type.
            if (string.IsNullOrEmpty(error))
            {
                if (this.priceType.SelectedIndex < 0)
                {
                    error = "Please select Price Type";
                }
                else
                {
                    item = (ComboboxItem)this.priceType.SelectedItem;
                    delivery_id = item.Value.ToString().Trim();
                }
            }

            /// Check Currency.
            if (string.IsNullOrEmpty(error))
            {
                if (this.currencySymbol.SelectedIndex < 0)
                {
                    error = "Please select Currency";
                }
                else
                {
                    item = (ComboboxItem)this.currencySymbol.SelectedItem;
                    currency = item.Value.ToString().Trim();
                }
            }

            /// Check exchange.
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.exchangeRate.Text))
                {
                    error = "Please enter Exchange Rate";
                }
                else
                {
                    if (float.TryParse(this.exchangeRate.Text.Trim(), out exchange))
                    {
                        if (exchange < 0)
                        {
                            error = "Exchange Rate must be greater than 0";
                        }
                    }
                    else
                    {
                        error = "Invalid Exchange Rate";
                    }
                }
            }           
            

            /// Check Quantity 1            
            if (string.IsNullOrEmpty(error))
            {                
                if (string.IsNullOrEmpty(quantity1.Text.Trim()))
                {
                    error = "Plase enter Quantity 1";                    
                }
                else
                {                    
                    if(int.TryParse(quantity1.Text.Trim(), out valueQuantity1))                    
                    {
                        if (valueQuantity1 <= 0)
                        {
                            error = "Quantity 1 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Quantity 1 must be numeric value";                        
                    }
                }
            }

            /// Check Price 1            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(price1.Text.Trim()))
                {
                    error = "Plase enter Price 1";                    
                }
                else
                {
                    if(float.TryParse(price1.Text.Trim(), out valuePrice1))                    
                    {
                        if (valuePrice1 <= 0)
                        {
                            error = "Price 1 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Price 1 must be numeric value";                        
                    }
                }
            }

            /// Check Quantity 2            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(quantity2.Text.Trim()))
                {
                    valueQuantity2 = valueQuantity1;                    
                }
                else
                {
                    if(int.TryParse(quantity2.Text.Trim(), out valueQuantity2))                    
                    {
                        if (valueQuantity2 <= 0)
                        {
                            error = "Quantity 2 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Quantity 2 must be numeric value";                        
                    }
                }
            }

            /// Check Quantity 3            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(quantity3.Text.Trim()))
                {
                    valueQuantity3 = valueQuantity2; 
                }
                else
                {
                    if(int.TryParse(quantity3.Text.Trim(), out valueQuantity3))                    
                    {
                        if (valueQuantity3 <= 0)
                        {
                            error = "Quantity 3 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Quantity 3 must be numeric value";                       
                    }
                }
            }

            /// Check Quantity 4            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(quantity4.Text.Trim()))
                {
                    valueQuantity4 = valueQuantity3; 
                }
                else
                {
                    if(int.TryParse(quantity4.Text.Trim(), out valueQuantity4))                    
                    {
                        if (valueQuantity4 <= 0)
                        {
                            error = "Quantity 4 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Quantity 4 must be numeric value";                        
                    }
                }
            }

            /// Check Quantity 5            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(quantity5.Text.Trim()))
                {
                    valueQuantity5 = valueQuantity4;
                }
                else
                {
                    if(int.TryParse(quantity5.Text.Trim(), out valueQuantity5))                    
                    {
                        if (valueQuantity5 <= 0)
                        {
                            error = "Quantity 5 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Quantity 5 must be numeric value";                        
                    }
                }
            }
            
            /// Check Price 2            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(price2.Text.Trim()))
                {
                    valuePrice2 = valuePrice1;
                }
                else
                {
                    if(float.TryParse(price2.Text.Trim(), out valuePrice2))
                    {
                        if (valuePrice2 <= 0)
                        {
                            error = "Price 2 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Price 2 must be numeric value";                        
                    }
                }
            }

            /// Check Price 3            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(price3.Text.Trim()))
                {
                    valuePrice3 = valuePrice2;
                }
                else
                {
                    if(float.TryParse(price3.Text.Trim(), out valuePrice3))                    
                    {
                        if (valuePrice3 <= 0)
                        {
                            error = "Price 3 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Price 3 must be numeric value";                        
                    }
                }
            }

            /// Check Price 4            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(price4.Text.Trim()))
                {
                    valuePrice4 = valuePrice3;
                }
                else
                {
                    if(float.TryParse(price4.Text.Trim(), out valuePrice4))                    
                    {
                        if (valuePrice4 <= 0)
                        {
                            error = "Price 4 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Price 4 must be numeric value";                        
                    }
                }
            }

            /// Check Price 5            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(price5.Text.Trim()))
                {
                    valuePrice5 = valuePrice4;
                }
                else
                {
                    if(float.TryParse(price5.Text.Trim(), out valuePrice5))                    
                    {
                        if (valuePrice5 <= 0)
                        {
                            error = "Price 5 must be > 0";                            
                        }
                    }
                    else
                    {
                        error = "Price 5 must be numeric value";                        
                    }
                }
            }

            /// Check Increase Quantity && Decrease Price
            if (!quantityBasePrice.Checked)
            {
                if (string.IsNullOrEmpty(error))
                {
                    if (valueQuantity1 > valueQuantity2)
                    {
                        error = "Quantity 2 must be greater than Quantity 1";                        
                    }
                }

                if (string.IsNullOrEmpty(error))
                {
                    if (valueQuantity2 > valueQuantity3)
                    {
                        error = "Quantity 3 must be greater than Quantity 2";                        
                    }
                }

                if (string.IsNullOrEmpty(error))
                {
                    if (valueQuantity3 > valueQuantity4)
                    {
                        error = "Quantity 4 must be greater than Quantity 3";                        
                    }
                }

                if (string.IsNullOrEmpty(error))
                {
                    if (valueQuantity4 > valueQuantity5)
                    {
                        error = "Quantity 5 must be greater than Quantity 4";                        
                    }
                }

                if (string.IsNullOrEmpty(error))
                {
                    if (valuePrice1 < valuePrice2)
                    {
                        error = "Price 1 must be greater than Price 2";                        
                    }
                }

                if (string.IsNullOrEmpty(error))
                {
                    if (valuePrice2 < valuePrice3)
                    {
                        error = "Price 2 must be greater than Price 3";                        
                    }
                }

                if (string.IsNullOrEmpty(error))
                {
                    if (valuePrice3 < valuePrice4)
                    {
                        error = "Price 3 must be greater than Price 4";                        
                    }
                }

                if (string.IsNullOrEmpty(error))
                {
                    if (valuePrice4 < valuePrice5)
                    {
                        error = "Price 4 must be greater than Price 5";                        
                    }
                }
            }            

            ///////////////////////////////////////////////////////
            /// Update Robot Quotation
            ///////////////////////////////////////////////////////
            if (string.IsNullOrEmpty(error))
            {
                note = this.noteText.Text;
                try
                {
                    myConnection.Open();
                    string sql = "UPDATE [241_RobotPrice]"
                        + " SET SupplierID='" + supplier_id + "', CurrencyID='" + currency + "', ExchangeRate=" + exchange + ","
                        + " Quantity1=" + valueQuantity1 + ", Quantity2=" + valueQuantity2 + ", Quantity3=" + valueQuantity3 + ","
                        + " Quantity4=" + valueQuantity4 + ", Quantity5=" + valueQuantity5 + ","
                        + " Price1=" + valuePrice1 + ", Price2=" + valuePrice2 + ", Price3=" + valuePrice3 + ","
                        + " Price4=" + valuePrice4 + ", Price5=" + valuePrice5 + ", PriceType='" + delivery_id + "',"
                        + " Note='" + note + "' WHERE ID=" + id;
                    SqlCommand cmd_sql = myConnection.CreateCommand();                    
                    cmd_sql.CommandText = sql;
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("Quotation Updated success.");
                    this.Dispose();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot Update Quotation. Please try again.");
                } 
            }
            else
            {
                if (!string.IsNullOrEmpty(error))
                {
                    MessageBox.Show(error);
                }
            } 
        }
        
        private void clearForm()
        {
            foreach (Control c in this.Controls)
            {
                if (c is DevExpress.XtraEditors.TextEdit)
                {
                    ((DevExpress.XtraEditors.TextEdit)c).Text = null;
                }

                if (c is DevExpress.XtraEditors.ComboBoxEdit)
                {
                    ((DevExpress.XtraEditors.ComboBoxEdit)c).SelectedIndex = -1;
                }

                if (c is System.Windows.Forms.MaskedTextBox)
                {
                    ((System.Windows.Forms.MaskedTextBox)c).Text = null;
                }
            }
        }

        private void quantityBasePrice_CheckedChanged(object sender, EventArgs e)
        {
            if (this.quantityBasePrice.Checked)
            {                
                this.quantity2.Enabled = true;
                this.quantity3.Enabled = true;
                this.quantity4.Enabled = true;
                this.quantity5.Enabled = true;                
                this.price2.Enabled = true;
                this.price3.Enabled = true;
                this.price4.Enabled = true;
                this.price5.Enabled = true;
            }
            else
            {
                this.quantity2.Text = null;
                this.quantity3.Text = null;
                this.quantity4.Text = null;
                this.quantity5.Text = null;                
                this.quantity2.Enabled = false;
                this.quantity3.Enabled = false;
                this.quantity4.Enabled = false;
                this.quantity5.Enabled = false;
                this.price2.Text = null;
                this.price3.Text = null;
                this.price4.Text = null;
                this.price5.Text = null;
                this.price2.Enabled = false;
                this.price3.Enabled = false;
                this.price4.Enabled = false;
                this.price5.Enabled = false;
            }
        }

        private void compID_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.supplierID.SelectedIndex = -1;
            // Get Supplier for selected Comp
            if (this.compID.SelectedIndex >= 0)
            {
                ComboboxItem item = (ComboboxItem)this.compID.SelectedItem;
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT SupplierID FROM [222_Part] WHERE PartNo='" + item.Value.ToString().Trim() + "'";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader["SupplierID"] != DBNull.Value)
                        {
                            for (int i = 0; i < this.supplierID.Properties.Items.Count; i++)
                            {
                                ComboboxItem sup = (ComboboxItem)this.supplierID.Properties.Items[i];
                                if (sup.Value.ToString().Trim() == dataReader["SupplierID"].ToString().Trim())
                                {
                                    this.supplierID.SelectedIndex = i;
                                }
                            }
                        }
                    }
                    myConnection.Close();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                }
            }
        }

        private void currencySymbol_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.exchangeRate.Text = null;
            if (this.currencySymbol.SelectedIndex >= 0)
            {
                ComboboxItem curr = (ComboboxItem)this.currencySymbol.SelectedItem;
                if (curr.Value.ToString() == "USD")
                {
                    this.exchangeRate.Text = "1";
                }
                else
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand sql_cmd = myConnection.CreateCommand();
                        sql_cmd.CommandText = "SELECT ExchangeRate"
                            + " FROM [222_ExchangeRate] WHERE CurrencyID='" + curr.Value.ToString().Trim() + "' AND ExchangeDate IN"
                            + " (SELECT MAX(ExchangeDate) FROM [222_ExchangeRate]"
                            + " WHERE CurrencyID = '" + curr.Value.ToString().Trim() + "' GROUP BY CurrencyID)";
                        SqlDataReader dataReader = sql_cmd.ExecuteReader();
                        if (dataReader.Read())
                        {
                            if (dataReader["ExchangeRate"] != DBNull.Value)
                            {
                                this.exchangeRate.Text = dataReader["ExchangeRate"].ToString().Trim();
                            }
                        }
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                    }
                }
            }
        }
    }
}
